import datetime
import math
import os
import sys
import time
from itertools import groupby
from operator import itemgetter

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.db.ProdDb import ProdDb
from src.db.FCDB import FCDB
from src.utils import CommonUtils, BatchUtils
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger


class CalcSecurities:
    """
    计算债券相关的数据
    """

    def __init__(self, logger, localDb, fcdb):
        self.logger = logger
        self.fcdb = fcdb
        self.localDb = localDb
        self.size = 10000

    #
    def __queryCbestimateAll__(self, secodes, maxTradeDate):
        """
        抽取指定日期之前的全部中债估值数据
        :param secodes:
        :param maxTradeDate:
        :return:
        """
        sql = """
            SELECT TRADEDATE, SECODE, YIELD, NETPRICE
            FROM tq_qt_cbestimate  FORCE INDEX (index_SECODE_TRADEDATE)
            WHERE DATASOURCE = '1' AND SECODE in %s AND TRADEDATE <= %s
            ORDER BY SECODE, TRADEDATE DESC
        """
        return self.fcdb.Query(sql, (secodes, maxTradeDate))

    def __queryCbestimate__(self, secodes, lastTradeDate):
        """
        适用指定交易日\n
        抽取指定日期之前的最新一条中债估值数据
        :param secodes:
        :param lastTradeDate:
        :return:
        """
        sql = """
            SELECT TRADEDATE, SECODE, YIELD, NETPRICE, ROW_NUMBER() OVER (PARTITION BY SECODE ORDER BY TRADEDATE DESC) AS NO
            FROM tq_qt_cbestimate FORCE INDEX (index_SECODE_TRADEDATE)
            WHERE DATASOURCE = '1' AND SECODE in %s AND TRADEDATE = %s
            ORDER BY SECODE, TRADEDATE DESC
        """
        return self.fcdb.Query(sql, (secodes, lastTradeDate))

    def __queryCbestimateLast__(self, secodes, lastTradeDate):
        """
        适用指定交易日\n
        抽取指定日期之前的最新一条中债估值数据
        :param secodes:
        :param lastTradeDate:
        :return:
        """
        sql = """
               SELECT * FROM (
               SELECT TRADEDATE, SECODE, YIELD, NETPRICE, ROW_NUMBER() OVER (PARTITION BY SECODE ORDER BY TRADEDATE DESC) AS NO
               FROM tq_qt_cbestimate FORCE INDEX (index_SECODE_TRADEDATE)
               WHERE DATASOURCE = '1' AND SECODE in %s AND TRADEDATE <= %s
               ) t WHERE t.no = 1
               ORDER BY SECODE, TRADEDATE DESC
           """
        return self.fcdb.Query(sql, (secodes, lastTradeDate))

    # 从数据源获取债券历史日交易信息
    def __queryRemoteBddailypriceDatas__(self, maxId):
        sql = """
               SELECT ID, TRADEDATE,SECODE,CYIELD,CLOSENETPRICE,VOL,AMOUNT
               FROM tq_qt_bddailyprice
               WHERE ISVALID = 1 and ID>%s
               ORDER BY ID
               Limit 100000
               """
        return self.fcdb.Query(sql, (maxId,))

    def __queryLocalMaxBddailypriceId__(self):
        sql = "SELECT MAX(fcdb_data_id) maxid from deb_transaction_details"
        r = self.localDb.Query(sql)
        if r[0]["maxid"] is not None:
            return r[0]["maxid"]
        return 0

    def __insert__(self, insertDatas: list):
        sql = """INSERT INTO deb_transaction_details
                (secode, transaction_date, closing_yield, yield_deviation, net_closing_price, net_price_deviation, turnover, 
                transaction_amount, netprice, sync_flag, fcdb_data_id, tradedate)
                VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
                  """
        return self.localDb.ExecMany(sql, insertDatas)

    # 查询计算用基础数据
    def __queryBaseDatas__(self):
        sql = """
            SELECT id, secode, closing_yield, net_closing_price, tradedate
            FROM deb_transaction_details
            WHERE sync_flag = 0
            ORDER BY secode 
            limit 50000
        """
        return self.localDb.Query(sql)

    def __update__(self, updateDatas: list):
        sql = "update deb_transaction_details set yield_deviation=%s, net_price_deviation=%s, netprice=%s, sync_flag=%s where id = %s;"
        sqlList = []
        for updateData in updateDatas:
            sqlList.append(sql % updateData)
        preSql = "".join(sqlList).replace("None", 'NULL')
        return self.localDb.ExecNoneQuery(preSql)

    @staticmethod
    def __buildInsertData__(d):
        return (
            d["SECODE"],
            CommonUtils.strToDate(d["TRADEDATE"]),
            d["CYIELD"],
            None,
            d["CLOSENETPRICE"],
            None,
            d["VOL"],
            d["AMOUNT"],
            None,
            0,
            d["ID"],
            d["TRADEDATE"]
        )

    @staticmethod
    def __buildUpdateData__(d, np, bp, dd, sync_flag):
        return (
            bp,
            dd,
            np,
            sync_flag,
            d["id"]
        )

    @staticmethod
    def __findCb__(cbDatas, secode, tradeDate):
        if cbDatas is None or len(cbDatas) == 0:
            return None
        if secode not in cbDatas.keys():
            return None
        cbList = cbDatas[secode]
        # 对于倒序的交易日期，找到第一个比指定日期小的，就算命中
        for cb in cbList:
            if cb["TRADEDATE"] <= tradeDate:
                return cb
        return None

    # 同步新数据
    def sync(self):
        self.logger.info("========== 同步新数据 开始 ==========")
        while True:
            maxId = self.__queryLocalMaxBddailypriceId__()
            self.logger.info("当前 MaxBddailypriceId：%d" % maxId)
            # 获取数据源
            remoteList = self.__queryRemoteBddailypriceDatas__(maxId=maxId)
            insertDatas = list(self.__buildInsertData__(d) for d in remoteList)
            self.logger.info("新增数据：%d" % len(insertDatas))
            BatchUtils.batchInsert(self.__insert__, insertDatas, self.size)

            # 抽取待处理数据总量
            if len(remoteList) == 0:
                self.logger.warn("没有更多新数据")
                break
        self.logger.info("========== 同步新数据 结束 ==========")

    # 计算偏离值
    def calc(self):
        self.logger.info("========== 计算收益率偏离度和净价偏离度 开始 ==========")

        while True:
            # 加载待计算数据
            baseDatasAll = self.__queryBaseDatas__()
            if len(baseDatasAll) == 0:
                self.logger.warn("没有需要处理的数据。")
                break

            # 分组处理
            size = 100
            arr = [baseDatasAll[i:i + size] for i in range(0, len(baseDatasAll), size)]
            for baseDatas in arr:

                # 加载当批数据的中债估值
                secodes = list(set(d["secode"] for d in baseDatas))
                minTradeDate = min(d["tradedate"] for d in baseDatas)
                maxTradeDate = max(d["tradedate"] for d in baseDatas)
                self.logger.info("加载中债估值 交易日：%s-%s，SECODE数：%d" % (minTradeDate, maxTradeDate, len(secodes)))

                lastTradeDate = CommonUtils.DateToStr(CommonUtils.strToDate(maxTradeDate) + datetime.timedelta(days=-1))
                if minTradeDate == maxTradeDate:
                    cbDatasList = self.__queryCbestimate__(secodes, lastTradeDate)
                    # 未取到昨日中债估值数据的，再取一次历史最新数据
                    noDataSecodes = list(set(secodes) - set(d["SECODE"] for d in cbDatasList))
                    if len(noDataSecodes) > 0:
                        cbDatasList.extend(self.__queryCbestimateLast__(noDataSecodes, lastTradeDate))
                else:
                    cbDatasList = self.__queryCbestimateAll__(secodes, lastTradeDate)
                cbDatas = {k: list(reversed(sorted(v, key=itemgetter("TRADEDATE")))) for k, v in groupby(cbDatasList, key=itemgetter('SECODE'))}
                self.logger.info("加载中债估值 按记录计：%d, 按债券计：%d" % (len(cbDatasList), len(cbDatas)))

                self.logger.info("开始计算：%d" % len(baseDatas))
                updateDatas = []

                # 同步计算标记
                # sync_flag状态为  0：未计算，1：计算完毕，2：无估值数据，3：估值数据异常,4：中债估值净价异常,5：前台收盘收益率异常
                for baseData in baseDatas:
                    sync_flag = 1
                    # 确定采用的中债估值数据行
                    cb = self.__findCb__(cbDatas, baseData["secode"], baseData["tradedate"])
                    # 无中债估值
                    if cb is None:
                        updateDatas.append(self.__buildUpdateData__(baseData, None, None, None, 2))
                    else:
                        # 收益率偏离度（bp）=(前台收盘收益率-中债估值收益率)*100
                        cy = baseData["closing_yield"]
                        y = cb["YIELD"]
                        if cy is None:
                            bp = None
                            sync_flag = 5
                        elif y is None:
                            sync_flag = 3
                            bp = None
                        else:
                            bp = (cy - y) * 100

                        # 净价偏离度（%）=(收盘净价-中债估值净价)*100/中债估值净价
                        ncp = baseData["net_closing_price"]
                        np = cb["NETPRICE"]
                        if ncp is None:
                            dd = None
                            sync_flag = 3
                        elif np is None or np == 0:
                            dd = None
                            sync_flag = 4
                        else:
                            dd = (ncp - np) * 100 / np
                        updateDatas.append(self.__buildUpdateData__(baseData, np, bp, dd, sync_flag))

                # 更新处理
                self.logger.info("待更新数据：%d" % len(updateDatas))
                if len(updateDatas) > 0:
                    self.__update__(updateDatas)

        self.logger.info("========== 计算收益率偏离度和净价偏离度 结束 ==========")


def __main__():
    logger = Logger()
    try:
        t = time.perf_counter()
        localDb = ProdDb(logSql=False, autocommit=True)
        fcdb = FCDB(logSql=False, autocommit=True)
        cleanScreen()
        calc = CalcSecurities(logger=logger, localDb=localDb, fcdb=fcdb)
        # 同步数据
        calc.sync()
        # 计算结果
        calc.calc()
        cast = (time.perf_counter() - t)
        m = math.floor(cast / 60)
        s = cast - m * 60
        logger.info('计算结束 %s 总耗时: %d分%.3f秒' % (datetime.datetime.now(), m, s))
    except Exception as e:
        logger.error("程序发生异常")
        logger.error(e)


# 程序入口
if __name__ == "__main__":
    __main__()
